

DROP TABLE staff;
GO
CREATE TABLE staff (	
	staffID CHAR(6) NOT NULL PRIMARY KEY,
	staffName VARCHAR(200) NOT NULL,
	email VARCHAR(200) NOT NULL,
	password VARCHAR(200) NOT NULL,
	createdDate DATETIME NOT NULL,
	creatorID CHAR(6) NOT NULL,
	modifiedDate DATETIME NULL,
	modifyerID CHAR(6) NULL,
	deleteFlg BIT NULL
) ;
GO
DROP TABLE department;	
GO
CREATE TABLE department (	
	deptID CHAR(3) NOT NULL PRIMARY KEY,
	deptName VARCHAR(200) NOT NULL,
	parentDeptID CHAR(3) NULL,
	createdDate DATETIME NOT NULL,
	creatorID CHAR(6) NOT NULL,
	modifiedDate DATETIME NULL,
	modifyerID CHAR(6) NULL,
	deleteFlg BIT NULL
) ;
DROP TABLE permission;	
CREATE TABLE permission (	
	permissionID CHAR(3) NOT NULL PRIMARY KEY,
	staffID CHAR(6) NOT NULL,
	deptID CHAR(3) NOT NULL,
	role VARCHAR(10) NOT NULL,
	createdDate DATETIME NOT NULL,
	creatorID CHAR(6) NOT NULL,
	modifiedDate DATETIME NULL,
	modifyerID CHAR(6) NULL,
	deleteFlg BIT NULL
) ;
DROP TABLE history;	
CREATE TABLE history (	
	historyID CHAR(20) NOT NULL PRIMARY KEY,
	poNumber CHAR(10) NOT NULL,
	updatedDate DATETIME NOT NULL,
	updatorID CHAR(6) NOT NULL,
	dataName VARCHAR(50) NOT NULL,
	changedData VARCHAR(200) NULL
) ;
DROP TABLE management;	
CREATE TABLE management (	
	poNumber CHAR(10) NOT NULL PRIMARY KEY,
	inputType VARCHAR(50) NULL,
	createdPODate DATETIME NULL,
	planDelDate DATETIME NULL,
	actualDelDate DATETIME NULL,
	saleDeptID CHAR(6) NULL,
	staffID CHAR(6) NULL,
	appID NUMERIC(6) NULL,
	appName VARCHAR(200) NULL,
	vendorID NUMERIC(6) NULL,
	vendorName VARCHAR(200) NULL,
	orderAmount NUMERIC(20) NULL,
	accDate DATETIME NULL,
	accAmount NUMERIC(20) NULL,
	maintainDeadline DATETIME NULL,
	returnAmount NUMERIC(20) NULL,
	accBalance NUMERIC(20) NULL,
	cusID NUMERIC(6) NULL,
	projectNo CHAR(20) NULL,
	prodDeptCode NUMERIC(3) NULL,
	prodDeptName VARCHAR(200) NULL,
	items CHAR(10) NULL,
	saleType CHAR(10) NULL,
	contractform NUMERIC(2) NULL,
	saleDiv CHAR(10) NULL,
	vendorUpdate CHAR(1) NULL,
	cusUpdate CHAR(1) NULL,
	checker VARCHAR(2) NULL,
	createDate DATETIME NOT NULL,
	creatorID CHAR(6) NOT NULL
);	

ALTER TABLE permission
ADD CONSTRAINT fk_Permission_Staff
FOREIGN KEY (staffID)
REFERENCES staff(staffID);

ALTER TABLE permission
ADD CONSTRAINT fk_Permission_Department
FOREIGN KEY (deptID)
REFERENCES department(deptID);

ALTER TABLE management
ADD CONSTRAINT fk_Management_Department
FOREIGN KEY (saleDeptID)
REFERENCES department(deptID);


ALTER TABLE management
ADD CONSTRAINT fk_Management_Staff
FOREIGN KEY (staffID)
REFERENCES staff(staffID);


ALTER TABLE history
ADD CONSTRAINT fk_History_Staff
FOREIGN KEY (updatorID)
REFERENCES staff(staffID);

ALTER TABLE department
ADD CONSTRAINT fk_Department_ParentDepartment
FOREIGN KEY (parentDeptID)
REFERENCES department(deptID);


ALTER TABLE history
ADD CONSTRAINT fk_History_Management
FOREIGN KEY (poNumber)
REFERENCES management(poNumber);

